﻿--use master
--go
--create database LibraryDB
--on primary
--(
--	name='LibraryDB_data',
--	filename='D:\data\Library\LibraryDB_data.mdf',
--	size=20MB,
--	filegrowth=10MB
--)
--log on
--(
--	name='LibraryDB_log',
--	filename='D:\data\Library\LibraryDB_log.ldf',
--	size=10MB,
--	filegrowth=5MB
--)
--go
--use LibraryDB
--go
--create table Categories
--(
--	CategoryId int identity(1,1) primary key,
--	CategoryName varchar(20)
--)
--go
--create table Publishers
--(
--	PublisherId int identity(1,1) primary key,
--	PublisherName varchar(20)
--)
--go
--create table ReaderRoles
--(
--	RoleId int identity(1,1) primary key,
--	RoleName varchar(20) not null,
--	AllowDay int not null,
--	AllowCounts int not null
--)
--go
--create table SysAdmins
--(
--	AdminId int identity(1000,1) primary key,
--	AdminName varchar(20) not null,
--	LoginPwd varchar(18) not null,
--	StatusId int default(1) not null,
--	RoleId int not null
--)
--go
--create table Books
--(
--	BookId int identity(1000000,1) primary key,
--	BarCode varchar(20) not null,
--	BookName varchar(100) not null,
--	Author varchar(20) not null,
--	PublisherId int references Publishers(PublisherId),
--	PublishDate smalldatetime not null,
--	BookCategory int references Categories(CategoryId),
--	UnitPrice numeric(18,2) not null,
--	BookImage text,
--	BookCount int,
--	Remainder int,
--	BookPosition varchar(20),
--	RegTime smalldatetime default(getdate())
--)
--go
--create table Readers
--(
--	ReaderId int identity(1,1) primary key,
--	ReadingCard varchar(20) unique,
--	ReaderName varchar(20) not null,
--	Gender char(2) not null,
--	IDCard char(18) not null,
--	ReaderAddress varchar(100) not null,
--	PostCode char(6),
--	PhoneNumber varchar(100) not null,
--	RoleId int references ReaderRoles(RoleId),
--	ReaderImage text,
--	RegTime datetime default(getdate()),
--	ReaderPwd varchar(18),
--	AdminId int references SysAdmins(AdminId),
--	StatusId int default(1) not null
--)
--go
--create table BorrowInfo
--(
--	BorrowId varchar(30) primary key,
--	ReaderId int references Readers(ReaderId),
--	BorrowDate datetime Default(getdate()),
--	AdminName_B varchar(20) not null
--)

--go
--create table BorrowDetail
--(
--	BorrowDetailId int identity(1000,1) primary key,
--	BorrowId varchar(30) references BorrowInfo(BorrowId),
--	BookId int references Books(BookId),
--	BorrowCount int null,
--	ReturnCount int default(0),
--	NoReturnCount int not null,
--	Expire datetime not null
--)
--go
--create table ReturnBook
--(
--	ReturnId int identity(1000,1) primary key,
--	BorrowDetailId int references BorrowDetail(BorrowDetailId),
--	ReturnCount int not null,
--	ReturnDate datetime default(getdate()),
--	Adminname_R varchar(20) null
--)
--go

use LibraryDB
go
--insert into Publishers(PublisherName) values('天津大学出版社')
--insert into Publishers(PublisherName) values('科学技术出版社')
--insert into Publishers(PublisherName) values('人民邮电出版社')
--insert into Publishers(PublisherName) values('经济技术出版社')
--insert into Publishers(PublisherName) values('人民教育出版社')
--insert into Publishers(PublisherName) values('北京大学出版社')
--insert into Publishers(PublisherName) values('南开大学出版社')
--go
insert into Categories(CategoryName) values('计算机软件')
insert into Categories(CategoryName) values('计算机硬件')
insert into Categories(CategoryName) values('计算机网络')
insert into Categories(CategoryName) values('软件工程')
insert into Categories(CategoryName) values('云计算')
insert into Categories(CategoryName) values('大数据')
insert into Categories(CategoryName) values('互联网技术')
insert into Categories(CategoryName) values('网络安全')
go

